﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using TeamNitro.Classes;

namespace TeamNitro.Classes
{
    public class ApprovalRequest
    {

        public int CDSRequestId { get; set; }        
        public String CDSCode { get; set; }
        public String CDSName { get; set; }
        public String ChangeType { get; set; }
//ChangeCDs
        public int ChangeCDSID { get; set; }
        public String CurrentCDSCode { get; set; }
        public String CurrentCDSName { get; set; }
        public String ChangeCDSCode { get; set; }
        public String ChangeCDSName { get; set; }

        public int ElectiveRequestID { get; set; }
        public String ElectiveCode { get; set; }
        public String ElectiveName { get; set; }
//ChangeElective
        public int ChangeElectiveID { get; set; }
        public String CurrentElectiveCode { get; set; }
        public String CurrentElectiveName { get; set; }
        public String ChangeElectiveCode { get; set; }
        public String ChangeElectiveName { get; set; }
//CommonAttributes
        public DateTime Date { get; set; }
        public String Comment { get; set; }
//StudentInfo
        public String StudentID { get; set; }
        public String StudentName { get; set; }
        public String StudentPassword { get; set; }
        public int StudentContactNum { get; set; }
        public String StudentAddress { get; set; }
        public String StudentEmail { get; set; }
        public String StudentCurrentYear { get; set; }
        public String StudentCurrentSem { get; set; }
        public String StudentSchool { get; set; }
        public String CourseName { get; set; }
        public String CourseCode { get; set; }
//StaffInfo
        public String StaffID { get; set; }
        public String StaffName { get; set; }
        public String StaffPassword { get; set; }
        public int StaffContactNum { get; set; }
        public String StaffAddress { get; set; }
        public String StaffEmail { get; set; }
        public String StaffPosition { get; set; }
        public String StaffSchool { get; set; }

 

        /// <summary>
        /// Elective Parts
        /// </summary>
        /// <returns></returns>

        public static DataTable GetRequestElective()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from RequestElectiveSubject";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable GetChangeElective()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from ChangeElectiveSubject";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static void InsertApprovedAddElective(ApprovalRequest ap)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO StudentElectiveSubject(StudentID, ElectiveCode, ElectiveName ) Values (@StudentID, @ElectiveCode, @ElectiveName )";
                comm.Parameters.AddWithValue("@StudentID", ap.StudentID);
                comm.Parameters.AddWithValue("@ElectiveCode", ap.ElectiveCode);
                comm.Parameters.AddWithValue("@ElectiveName", ap.ElectiveName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void InsertApprovedChangeElective(ApprovalRequest ap)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO StudentElectiveSubject(StudentID, ElectiveCode, ElectiveName ) Values (@StudentID, @ElectiveCode, @ElectiveName )";
                comm.Parameters.AddWithValue("@StudentID", ap.StudentID);
                comm.Parameters.AddWithValue("@ElectiveCode", ap.ElectiveCode);
                comm.Parameters.AddWithValue("@ElectiveName", ap.ElectiveName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static ApprovalRequest GetRequestElectiveByRequestID(String ElectiveRequestID)
        {
            SqlConnection conn = null;
            ApprovalRequest AppReq = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RequestElectiveSubject WHERE ElectiveRequestID=@ElectiveRequestID";
                comm.Parameters.AddWithValue("@ElectiveRequestID", ElectiveRequestID);
                SqlDataReader dr = comm.ExecuteReader();
                AppReq = new ApprovalRequest();

                while (dr.Read())
                {
                    AppReq.ElectiveRequestID = (int)dr["ElectiveRequestID"];
                    AppReq.StudentID = (String)dr["StudentID"];
                    AppReq.ElectiveCode = (String)dr["ElectiveCode"];
                    AppReq.ElectiveName = (String)dr["ElectiveName"];
                    AppReq.Date = (DateTime)dr["Date"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return AppReq;
        }

        public static ApprovalRequest GetChangeElectiveByChangeElectiveID(String ChangeElectiveID)
        {
            SqlConnection conn = null;
            ApprovalRequest AppReq = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM ChangeElectiveSubject WHERE ChangeElectiveID=@ChangeElectiveID";
                comm.Parameters.AddWithValue("@ChangeElectiveID", ChangeElectiveID);
                SqlDataReader dr = comm.ExecuteReader();
                AppReq = new ApprovalRequest();

                while (dr.Read())
                {
                    AppReq.ChangeElectiveID = (int)dr["ChangeElectiveID"];
                    AppReq.StudentID = (String)dr["StudentID"];
                    AppReq.CurrentElectiveCode = (String)dr["CurrentElectiveCode"];
                    AppReq.CurrentElectiveName = (String)dr["CurrentElectiveName"];
                    AppReq.ChangeElectiveCode = (String)dr["ChangeElectiveCode"];
                    AppReq.ChangeElectiveName = (String)dr["ChangeElectiveName"];
                    AppReq.Comment = (String)dr["Comment"];
                    AppReq.Date = (DateTime)dr["Date"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return AppReq;
        }

        

        /// <summary>
        /// CDs Parts
        /// </summary>
        /// <returns></returns>

        public static DataTable GetRequestCDs()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from RequestCDSSubject";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable GetChangeCDs()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from ChangeCDSSubject";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static void InsertApprovedAddCDs(ApprovalRequest ap)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO StudentCDSSubject (StudentID, CDSCode, CDSName) VALUES (@StudentID, @CDSCode, @CDSName)";
                comm.Parameters.AddWithValue("@StudentID", ap.StudentID);
                comm.Parameters.AddWithValue("@CDSCode", ap.CDSCode);
                comm.Parameters.AddWithValue("@CDSName", ap.CDSName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void InsertApprovedChangeCDs(ApprovalRequest ap)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO StudentCDSSubject (StudentID, CDSCode, CDSName) VALUES (@StudentID, @CDSCode, @CDSName)";
                comm.Parameters.AddWithValue("@StudentID", ap.StudentID);
                comm.Parameters.AddWithValue("@CDSCode", ap.CDSCode);
                comm.Parameters.AddWithValue("@CDSName", ap.CDSName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static ApprovalRequest GetRequestCDsByRequestID(String CDSRequestId)
        {
            SqlConnection conn = null;
            ApprovalRequest AppReq = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RequestCDSSubject WHERE CDSRequestId=@CDSRequestId";
                comm.Parameters.AddWithValue("@CDSRequestId", CDSRequestId);
                SqlDataReader dr = comm.ExecuteReader();
                AppReq = new ApprovalRequest();

                while (dr.Read())
                {                    
                    AppReq.CDSRequestId = (int)dr["CDSRequestId"];
                    AppReq.StudentID = (String)dr["StudentID"];
                    AppReq.CDSCode = (String)dr["CDSCode"];
                    AppReq.CDSName = (String)dr["CDSName"];
                    AppReq.ChangeType = (String)dr["ChangeType"];
                    AppReq.Date = (DateTime)dr["Date"];                    
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return AppReq;
        }

        public static ApprovalRequest GetChangeCDSByChangeCDSID(String ChangeCDSID)
        {
            SqlConnection conn = null;
            ApprovalRequest AppReq = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM ChangeCDSSubject WHERE ChangeCDSID=@ChangeCDSID";
                comm.Parameters.AddWithValue("@ChangeCDSID", ChangeCDSID);
                SqlDataReader dr = comm.ExecuteReader();
                AppReq = new ApprovalRequest();

                while (dr.Read())
                {
                    AppReq.ChangeCDSID = (int)dr["ChangeCDSID"];
                    AppReq.StudentID = (String)dr["StudentID"];
                    AppReq.CurrentCDSCode = (String)dr["CurrentCDSCode"];
                    AppReq.CurrentCDSName = (String)dr["CurrentCDSName"];
                    AppReq.ChangeCDSCode = (String)dr["ChangeCDSCode"];
                    AppReq.ChangeCDSName = (String)dr["ChangeCDSName"];
                    AppReq.Comment = (String)dr["Comment"];
                    AppReq.Date = (DateTime)dr["Date"];
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return AppReq;
        }

       

        /// <summary>
        /// Staff Parts
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>

        public static ApprovalRequest GetStaffInfo(string UserName)
        {
            SqlConnection conn = null;
            ApprovalRequest AppReq = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", UserName);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    AppReq = new ApprovalRequest();
                    AppReq.StaffID = (string)dr["StaffID"];
                    AppReq.StaffName = (string)dr["StaffName"];
                    AppReq.StaffPassword = (string)dr["Password"];
                    AppReq.StaffContactNum = (int)dr["ContactNumber"];
                    AppReq.StaffAddress = (string)dr["Address"];
                    AppReq.StaffEmail = (string)dr["Email"];
                    AppReq.StaffPosition = (string)dr["Position"];
                    AppReq.StaffSchool = (string)dr["School"];
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return AppReq;
        }       


        public static void UpdateStaffInfo(ApprovalRequest ap)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET StaffEmail=@Email, StaffContactNum=@ContactNumber StaffAddress=@Address WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", ap.StaffID);
                comm.Parameters.AddWithValue("@Email", ap.StaffEmail);
                comm.Parameters.AddWithValue("@ContactNumber", ap.StaffContactNum);
                comm.Parameters.AddWithValue("@Address", ap.StaffAddress);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

       

        /// <summary>
        /// Student Parts
        /// </summary>
        /// <param name="StudentID"></param>
        /// <returns></returns>
        public static DataTable GetStudentInfoByStudentID(string StudentID)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Student WHERE StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", StudentID);
                SqlDataReader dr = comm.ExecuteReader();

                dt = new DataTable();
                dt.Load(dr);
                dr.Close();

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        //public static void InsertApprovedChangedCDs(ApprovalRequest ap)
        //{
        //    SqlConnection conn = null;

        //    try
        //    {
        //        conn = new SqlConnection();
        //        conn.ConnectionString = "Data Source=.;Initial Catalog=subjectdb; Integrated Security=true";
        //        SqlCommand comm = new SqlCommand();
        //        conn.Open();
        //        comm.Connection = conn;
        //        comm.CommandText = "INSERT INTO ChangeCDSSubject (changeCDSID, StudentID, CurrentCDSCode, ChangeCDSCode, Comment, Date ) VALUES (@changeCDSID, @StudentID, @CurrentCDSCode, @ChangeCDSCode, @Comment, @Date)";
        //        comm.Parameters.AddWithValue("@changeCDSID", ap.changeCDSID);
        //        comm.Parameters.AddWithValue("@StudentID", ap.StudentID);
        //        comm.Parameters.AddWithValue("@CurrentCDSCode", ap.CurrentCDSCode);
        //        comm.Parameters.AddWithValue("@ChangeCDSCode", ap.ChangeCDSCode);
        //        comm.Parameters.AddWithValue("@Comment", ap.Comment);
        //        comm.Parameters.AddWithValue("@Date", ap.Date);
        //        int rowsAffected = comm.ExecuteNonQuery();
        //    }
        //    catch (SqlException e)
        //    {
        //        throw e;
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //}

      

        
       
    }
}